﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data;
using System.ComponentModel;

namespace SharpPopulatorLib 
{

    public class DBManager 
    {

        #region Constants

        private const String FieldQuery = "SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'";
        private const String KeyQuery = "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{0}'";
        private const String FieldQueryEntity = "SELECT * FROM {0}";

        #endregion

        #region Public Methods

        public static List<String> GetTables(string connection) 
        {
            //To obtain tables from the source
            List<String> tables = new List<String>();
            try 
            {
                using (SqlConnection cn = new SqlConnection(connection)) 
                {
                    cn.Open();
                    DataTable table = cn.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" });
                    foreach (DataRow row in table.Rows) 
                    { if (row["table_name"].ToString() != "dtproperties" && row["table_name"].ToString() != "sysdiagrams") { tables.Add(row["table_name"].ToString()); } }
                }
            } 
            catch (Exception exc) 
            { }
            return tables;
        }

        //public static List<DBField> GetFields(String tableName, String connection) 
        //{
        //    //To retrieve fields
        //    List<DBField> fields = new List<DBField>();
        //    DBField newfield;

        //    if (fields.Count > 0) fields.Clear();

        //    using (SqlConnection cn = new SqlConnection(connection)) 
        //    {
        //        using (SqlCommand cmd = new SqlCommand(String.Format(FieldQuery, tableName), cn)) 
        //        {
        //            cn.Open();
        //            SqlDataReader reader = cmd.ExecuteReader();
        //            if (reader.HasRows) 
        //            {
        //                while (reader.Read()) 
        //                {
        //                    newfield = new DBField();
        //                    newfield.Name = reader["COLUMN_NAME"].ToString();
        //                    if (reader["IS_NULLABLE"].ToString() == "NO") 
        //                    { newfield.IsNullable = false; } 
        //                    else 
        //                    { newfield.IsNullable = true; }
        //                    newfield.DataType = reader["DATA_TYPE"].ToString();
        //                    if (String.IsNullOrEmpty(reader["CHARACTER_MAXIMUM_LENGTH"].ToString())) 
        //                    { newfield.CharacterLenght = 0; } 
        //                    else 
        //                    { newfield.CharacterLenght = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"].ToString()); }
        //                    if (String.IsNullOrEmpty(reader["NUMERIC_PRECISION"].ToString())) 
        //                    { newfield.NumericPrecision = 0; } 
        //                    else 
        //                    { newfield.NumericPrecision = Convert.ToInt32(reader["NUMERIC_PRECISION"].ToString()); }
        //                    fields.Add(newfield);
        //                }
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //        }
        //    }
        //    return fields;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="connection"></param>
        /// <param name="Identity"></param>
        /// <returns></returns>
        public static List<DBField> GetFields(String tableName, String connection, Boolean Identity)
        {
            //
            List<DBField> fields = new List<DBField>();
            DataTable dtTemp = new DataTable();
            Boolean PKColumn = false;

            using (SqlConnection cn = new SqlConnection(connection))
            {
                using (SqlCommand cmd = new SqlCommand(String.Format(FieldQueryEntity, tableName), cn))
                {
                    cn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        da.FillSchema(dtTemp, SchemaType.Mapped);
                    }
                }
            }

            foreach(DataColumn sColumn in dtTemp.Columns)
            {
                foreach(DataColumn _ColumnKey in dtTemp.PrimaryKey)
                { 
                    if (_ColumnKey.ColumnName == sColumn.ColumnName)
                    { PKColumn = true; }
                    else
                    { PKColumn = false; }
                }
                fields.Add(new DBField(sColumn.ColumnName, sColumn.DataType.Name, sColumn.AllowDBNull, sColumn.MaxLength, PKColumn, sColumn.Unique));
            }

            return fields;
        }

        public static Boolean PopulateDB(String ScriptFileName)
        {
            //
            using (System.IO.StreamReader _sqlFile = new System.IO.StreamReader(ScriptFileName))
            {
                if (!_sqlFile.EndOfStream)
                {
                    using (SqlConnection _cn = new SqlConnection(Settings.ConnectionString))
                    {
                        _cn.Open();
                        using (SqlCommand _cmd = new SqlCommand(_sqlFile.ReadToEnd(), _cn))
                        {
                            try
                            {
                                _cmd.ExecuteNonQuery();
                                return true;
                            }
                            catch (Exception)
                            { throw; }
                        }
                    }
                }
                else
                { throw new ArgumentNullException("It looks like that there's no SQL commands to execute."); }
            }

        }

        #endregion

        #region Private Methods

       

        #endregion

    }

    public class DBField 
    {

        #region Atributes

        private String _name;
        private String _datatype;
        private Boolean _isnullable;
        private Int32 _maxlength;
        private Boolean _isprimarykey;
        private Boolean _isunique;

        #endregion

        #region Properties

        [Category(""), Description("")]
        public String Name 
        {
            get { return _name; }
            set { _name = value; }
        }

        [Category(""), Description("")]
        public Boolean IsNullable 
        {
            get { return _isnullable; }
            set { _isnullable = value; }
        }

        [Category(""), Description("")]
        public String DataType 
        {
            get { return _datatype; }
            set { _datatype = value; }
        }

        [Category(""), Description("")]
        public Int32 MaxLength 
        {
            get { return _maxlength; }
            set { _maxlength = value; }
        }

        [Category(""), Description("")]
        public Boolean IsPrimaryKey 
        {
            get { return _isprimarykey; }
            set { _isprimarykey = value; }
        }

        [Category(""), Description("")]
        public Boolean IsUnique
        {
            get { return _isunique; }
            set { _isunique = value; }
        }

        #endregion

        #region Constructors

        /// <summary>
        /// To create an instance of the class
        /// </summary>
        public DBField() 
        {
            //Defualt constrcutor
        }

        /// <summary>
        /// To create an instance of the class
        /// </summary>
        /// <param name="Name">field's name</param>
        /// <param name="Type">field's data type</param>
        /// <param name="Nullable">Indicates if the field acepts null values</param>
        /// <param name="Lenght">Character Length of the field</param>
        /// <param name="Precision">Numeric precision of the field</param>
        public DBField(String Name, String Type, Boolean Nullable, Int32 MaxLength, Boolean IsPrimaryKey, Boolean IsUnique) 
        {
            //overload of the default constructo
            if (!String.IsNullOrEmpty(Name) && !String.IsNullOrEmpty(Type)) 
            {
                _name = Name;
                _datatype = Type;
                _isnullable = Nullable;
                _maxlength = MaxLength;
                _isprimarykey = IsPrimaryKey;
                _isunique = IsUnique;
            } 
            else 
            { throw new ArgumentException(""); }

        }

        #endregion

        #region Private methods
        #endregion

        #region Public methods
        #endregion

    }

}
